Query Form Substitution Parameters

You can use Query Form Substitution Parameters to create advanced filters based on a parameters submitted by the end user.

For example, the following dashboard which displays a grid to compare the total number of orders each year against the number of orders measured over base and comparison dates. The grid displays the following columns:

• Order Total: the total number of orders per year
• Base Month: The total number of orders after or during the base month
• Comparison Month: The total number of orders before or during the comparison month

Using substitution parameters, users can specify the base and comparison dates so that the grid can be filtered dynamically in 'view' mode, using the dashboard's query form. For example:

The following sections describe the query form and grid used in the above examples and can be used as a guide to adding substitution parameters to your own dashboard filters.

When you have finished configuring the query form, drag it into the dashboard's query form area. You must then add the substitution parameters defined in this query form to the grid's measures.

Configuring the Query Form

To enable users to specify values for the base and comparison dates, create a query form containing two substitution parameters to define the base and comparison dates to be entered by the user including descriptive text:

• p_Base
• p_Comparison

Add a Parameter Box for each substitution parameter, each of which contains a Yes No Question element. Each question is used to define a query to filter the Base Month and Comparison Month grid columns, using the substitution parameter values provided by the user. The questions must also be configured with some descriptive text to describe each query:

• The first parameter box p_BaseQuery must return orders where the order date is greater than or equal to the p_Base parameter specified by the user:

• The second parameter box, p_ComparisonQuery, requires a filter to return orders where the order date is less than or equal to the p_Comparison parameter specified by the user:

The following screenshot displays the final query form:

Configuring the Grid

The dashboard displays a simple grid to show the order year against three measures; customer total, total customers since the base month and total customers up until the comparison month. As such, the base and comparison month measures must be filtered by the p_Base and p_Comparison substitution parameters. This enables the results to be sorted according to the base and comparison dates entered by the user. When adding substitution parameters to a filter, they must be enclosed in braces {}. For example, to create the base month column, you would configure a measure with the following filter:

To configure the Comparison month column, you would require the following measure:

When you have finished configuring the grid (having ensured that you have added the substitution parameters to the measure filters), the dashboard configuration is complete and users can filter the grid by specifying the required base and comparison dates in the dashboard's query form.

• Filter
  • Query Form Substitution Parameters
  • Configuring Global Filters